<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
<head>
<!--

 *************************************************************************
 * Copyright (c) 2004, 2006 Actuate Corporation.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *  Actuate Corporation - initial API and implementation
 *  
 *************************************************************************

-->
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="white">

[<b>Non-API</b>] Flat file data source provider - an ODA run-time extension.
<p>
Note: <i>The implementation classes are <b>not</b> public APIs.  
<br>Backward compatibility support in future releases is not guaranteed.</i>

<h2>Package Specification</h2>
The ODA flat file driver serves as an exemplary implementation
of the ODA run-time interfaces.  
It performs basic data source provider functionalities including:
<p>
<ul>
<li>Executes a query on a specific flat file(CSV, SSV, TSV, PSV) using SQL-like query syntax</li>
<li>Provides the query's result set metadata</li>
<li>Retrieves the query's result set data from the CSV data file</li>
</ul>
<p>
<h2>Consuming the ODA flat file driver</h2>
<p>
<ul>
<li>Start by creating an IConnection instance by the FlatFileDriver.getConnection method.</li>
<li>Open the connection using the IConnection.open( Properties prop ) method. 
The driver-specific connection property names are:
<ul>
<li>"HOME" : The directory of the flat file (required property)</li>
<li>"CHARSET" : The character set for decoding the data file; default value= "UTF-8"</li>
<li>"INCLCOLUMNNAME": Indicates whether the  flat file contains column name meta-data; valid values= "YES" (default), "NO"</li>
<li>"INCLTYPELINE" : Indicates whether the flat file contains data type meta-data; valid values= "YES" (default), "NO"</li>
<li>"DELIMTYPE": Indicates the delimiter type the flat file uses; valid values="COMMA"(default), "SEMICOLON", "TAB", "PIPE"</li>
</ul>
<li>Create an IQuery instance by the IConnection.newQuery( String dataSetType ) method.</li>
<li>Execute the query by the IQuery.executeQuery method, which returns an IResultSet for data retrieval.</li>
</ul>
</p>

<h2>Data store format</h2>
<p>
The flat file ODA driver expects that both meta-data (including column names 
and data types) and data are kept in a single flat file.
The first line of a flat file specifies data column names.
The second line may optionally specify the column data types. 
The remaining portion of the file contains data.
</p>
<p>
<h3>Redundant Spaces</h3>
Redundant spaces are allowed in a flat file, but will be trimmed once processed by the flat file driver.
</p>
<h3>Double Quotes</h3>
Double Quotes can be used in a flat file for the purpose of clarity. 
The quotes, however, will be trimmed once processed by the driver. 
That is, a line in flat file like
<ul>100,"I'm a string","1"</ul>
is processed to be the same as the following line:
<ul>100,I'm a string,1</ul>

A comma within a pair of double quotes would not be treated as separator. 
For example,
<ul>"I'm, however, a really normal String"</ul>
contains a single column value.  
It is not considered the same as the following line:
<ul>I'm, however, a really normal String</ul>
because the second case is processed to contain three columnn values, 
i.e. "I'm", "however", and "a really normal String".
<p>
<h3>Null Values</h3>
Null values are allowed. They are presented as blanks, and are comma-separated from other data. 
A flat file that contains only one column, and all the data is of null value will be treated as an empty table.
</p> 

<h2>Data Types</h2>

Flat file driver currently supports the following data types: 
<br>INT, DOUBLE, STRING, DATE, TIME, TIMESTAMP and BIGDECIMAL. 
<br>Support for BLOB and CLOB data types will be added in future.
The driver's data type codes are defined as follows:
<p>
<ul>    INT = java.sql.Types.INTEGER;</ul>
<ul>	DOUBLE = java.sql.Types.DOUBLE;</ul>
<ul>	STRING = java.sql.Types.VARCHAR;</ul>
<ul>	DATE = java.sql.Types.DATE;</ul>
<ul>	TIME = java.sql.Types.TIME;</ul>
<ul>	TIMESTAMP = java.sql.Types.TIMESTAMP;</ul>
<ul>	BIGDECIMAL = java.sql.Types.NUMERIC;</ul>
<ul>	BLOB = java.sql.Types.BLOB;</ul>
<ul>	CLOB = java.sql.Types.CLOB;</ul>
</p>

<h2>SQL-like Query Syntax</h2>

The flat file driver supports limited SQL-like query syntax. 
The supported syntax is:
<br>(those in square brackets are optional):
<ul>
<li><b>SELECT</b> column1 [<b>AS</b> alias1] [,column2 [<b>AS</b> alias2]]... [,columnN [<b>AS</b> aliasN]] <b>FROM</b> tableName</li>
</ul>
<ul>
<li><b>SELECT * FROM</b> tableName</li>
</ul>
The query text is case in-sensitive and allows redundant spaces. 
The flat file driver does not support multiple tables in the FROM clause.  
For example, a table is named "employee.csv" with columns "Id", "Name" and "HireDate",
the following queries are valid:

<ul>SELECT Id FROM employee.csv</ul>
<ul>SELECT Id,Name FROM employee.csv</ul>
<ul>SELECT Id,Name,HireDate FROM employee.csv</ul>

<ul>SELECT * FROM EMPLOYEE</ul>

<ul>select Id AS PersonnelId, Name AS EmployeeName FROM employee.csv</ul>
<ul>SELECT Name    AS     EmployeeName ,   Id  , HireDate  FROM employee.csv</ul>
<ul>SELECT name, name from employee.csv 
<br>// This command is valid, but would simply return a result set of two columns with same data values.</ul>

However, the following queries are invalid:
<ul>Id FROM EMPLOYEE.csv  			<br>// missing keyword "SELECT"</ul>
<ul>Select id       			  <br>// missing keyword "FROM"</ul>
<ul>SELECT I FROM employee.csv	<br>// invalid column name</ul>
<br><br>
<b>Advanced SOL-like query-text is also defined, and it can be used in the dataset design, 
for detailed information please refer to "<i>org.eclipse.datatolls.connectivity.oda.flatfile.util.querytextutil.package.html</i>"</b>
<br><br>
<!-- Put @see and @since tags down here. -->
@since 3.0

</body>
</html>
